/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package inet.dao;

import static inet.db.pool.AaaDBPool.getConnection;
import static inet.db.pool.AaaDBPool.releaseConnection;
import inet.entity.Employee;
import inet.entity.TimeKeepings;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import inet.ulti.DateTimeUlti;

/**
 *
 * @author ÂuNgọcThái
 */
public class TimeKeepingsDAO extends BaseDAO {

    public List<TimeKeepings> getAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        List<TimeKeepings> lstTimeKeep = null;
        try {
            sql = "SELECT TK_ID,  A.EMP_ID,  TK_NAME,  DAY1,  DAY2,  DAY3,  DAY4,  DAY5,  DAY6,  DAY7,  DAY8,  DAY9,  "
                    + "DAY10,  DAY11,  DAY12,  DAY13,  DAY14,  DAY15, DAY16,  DAY17,  DAY18,  DAY19,  DAY20,  DAY21, "
                    + " DAY22,  DAY23,  DAY24,  DAY25,  DAY26,  DAY27,  DAY28,  DAY29,  DAY30,  DAY31,"
                    + "  EMP_CODE,FIRST_NAME,LAST_NAME "
                    + "FROM TIMEKEEPINGS A JOIN EMPLOYEE B ON B.EMP_ID = A.EMP_ID";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            TimeKeepings timekeep = null;
            while (rs.next()) {
                timekeep = new TimeKeepings();
                if (lstTimeKeep == null) {
                    lstTimeKeep = new ArrayList<TimeKeepings>();
                }
                int i = 1;
                timekeep.setTk_id(rs.getBigDecimal(i++));
                timekeep.setEmp_id(rs.getBigDecimal(i++));
                timekeep.setTk_name(rs.getString(i++));
                timekeep.setDay1(rs.getInt(i++));
                timekeep.setDay2(rs.getInt(i++));
                timekeep.setDay3(rs.getInt(i++));
                timekeep.setDay4(rs.getInt(i++));
                timekeep.setDay5(rs.getInt(i++));
                timekeep.setDay6(rs.getInt(i++));
                timekeep.setDay7(rs.getInt(i++));
                timekeep.setDay8(rs.getInt(i++));
                timekeep.setDay9(rs.getInt(i++));
                timekeep.setDay10(rs.getInt(i++));
                timekeep.setDay11(rs.getInt(i++));
                timekeep.setDay12(rs.getInt(i++));
                timekeep.setDay13(rs.getInt(i++));
                timekeep.setDay14(rs.getInt(i++));
                timekeep.setDay15(rs.getInt(i++));
                timekeep.setDay16(rs.getInt(i++));
                timekeep.setDay17(rs.getInt(i++));
                timekeep.setDay18(rs.getInt(i++));
                timekeep.setDay19(rs.getInt(i++));
                timekeep.setDay20(rs.getInt(i++));
                timekeep.setDay21(rs.getInt(i++));
                timekeep.setDay22(rs.getInt(i++));
                timekeep.setDay23(rs.getInt(i++));
                timekeep.setDay24(rs.getInt(i++));
                timekeep.setDay25(rs.getInt(i++));
                timekeep.setDay26(rs.getInt(i++));
                timekeep.setDay27(rs.getInt(i++));
                timekeep.setDay28(rs.getInt(i++));
                timekeep.setDay29(rs.getInt(i++));
                timekeep.setDay30(rs.getInt(i++));
                timekeep.setDay31(rs.getInt(i++));
                timekeep.setEmp(new Employee(rs.getBigDecimal(2), rs.getString(i++), rs.getString(i++), rs.getString(i++), null, null, null, null, null, 0, null, null, 0, null, null, 0, null));
                lstTimeKeep.add(timekeep);
            }
            return lstTimeKeep;
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return null;
    }

    public List<TimeKeepings> getAllByID(BigDecimal id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        List<TimeKeepings> lstTimeKeep = null;
        try {
            sql = "SELECT TK_ID,  A.EMP_ID,  TK_NAME,  DAY1,  DAY2,  DAY3,  DAY4,  DAY5,  DAY6,  DAY7,  DAY8,  DAY9,  "
                    + "DAY10,  DAY11,  DAY12,  DAY13,  DAY14,  DAY15, DAY16,  DAY17,  DAY18,  DAY19,  DAY20,  DAY21, "
                    + " DAY22,  DAY23,  DAY24,  DAY25,  DAY26,  DAY27,  DAY28,  DAY29,  DAY30,  DAY31,"
                    + "  EMP_CODE,FIRST_NAME,LAST_NAME,ROLL "
                    + "FROM TIMEKEEPINGS A JOIN EMPLOYEE B ON B.EMP_ID = A.EMP_ID WHERE B.EMP_ID=?";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setBigDecimal(1, id);
            rs = ps.executeQuery();
            TimeKeepings timekeep = null;
            while (rs.next()) {
                timekeep = new TimeKeepings();
                if (lstTimeKeep == null) {
                    lstTimeKeep = new ArrayList<TimeKeepings>();
                }
                int i = 1;
                timekeep.setTk_id(rs.getBigDecimal(i++));
                timekeep.setEmp_id(rs.getBigDecimal(i++));
                timekeep.setTk_name(rs.getString(i++));
                timekeep.setDay1(rs.getInt(i++));
                timekeep.setDay2(rs.getInt(i++));
                timekeep.setDay3(rs.getInt(i++));
                timekeep.setDay4(rs.getInt(i++));
                timekeep.setDay5(rs.getInt(i++));
                timekeep.setDay6(rs.getInt(i++));
                timekeep.setDay7(rs.getInt(i++));
                timekeep.setDay8(rs.getInt(i++));
                timekeep.setDay9(rs.getInt(i++));
                timekeep.setDay10(rs.getInt(i++));
                timekeep.setDay11(rs.getInt(i++));
                timekeep.setDay12(rs.getInt(i++));
                timekeep.setDay13(rs.getInt(i++));
                timekeep.setDay14(rs.getInt(i++));
                timekeep.setDay15(rs.getInt(i++));
                timekeep.setDay16(rs.getInt(i++));
                timekeep.setDay17(rs.getInt(i++));
                timekeep.setDay18(rs.getInt(i++));
                timekeep.setDay19(rs.getInt(i++));
                timekeep.setDay20(rs.getInt(i++));
                timekeep.setDay21(rs.getInt(i++));
                timekeep.setDay22(rs.getInt(i++));
                timekeep.setDay23(rs.getInt(i++));
                timekeep.setDay24(rs.getInt(i++));
                timekeep.setDay25(rs.getInt(i++));
                timekeep.setDay26(rs.getInt(i++));
                timekeep.setDay27(rs.getInt(i++));
                timekeep.setDay28(rs.getInt(i++));
                timekeep.setDay29(rs.getInt(i++));
                timekeep.setDay30(rs.getInt(i++));
                timekeep.setDay31(rs.getInt(i++));
                timekeep.setEmp(new Employee(rs.getBigDecimal(2), rs.getString(i++), rs.getString(i++), rs.getString(i++), null, null, null, null, null, 0, null, null, rs.getInt(i++), null, null, 0, null));
                lstTimeKeep.add(timekeep);
            }
            return lstTimeKeep;
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return null;
    }

    // Chấm công cho từng nv
    public boolean update(BigDecimal tkID, String tkS) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "UPDATE TIMEKEEPINGS SET DAY?= ?  WHERE TK_ID = ?";
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            int i = 1;
            ps.setString(i++, DateTimeUlti.dateNow());
            ps.setString(i++, tkS);
            ps.setBigDecimal(i++, tkID);
            return ps.executeUpdate() == 1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            releaseConnection(conn, ps, null);
        }
        return false;
    }


    public static void main(String[] a) {
        TimeKeepingsDAO test = new TimeKeepingsDAO();
        if (test.getAll() == null) {
            System.out.print("loi");
        } else {
            System.out.print("ok");
        }
    }

}
